package com.situ.achievementsystem.score.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.achievementsystem.score.model.ScoreModel;
import com.situ.achievementsystem.util.JDBCUtil;

public class ScoreDao {

	private Connection con = null;
	private PreparedStatement ps = null;
	
	public String insert(ScoreModel model) {
		String sql = "insert into achievements(name,score)values(?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getName());
			ps.setString(2,model.getScore());
			int res = ps.executeUpdate();
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String delete(ScoreModel model) {
		String sql = "delete from achievements where name = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getName());
			int res = ps.executeUpdate();
			return res+"";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String update(ScoreModel model) {
		String sql = "update achievements set score =? where name=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(2,model.getName());
			ps.setString(1,model.getScore());
			int res = ps.executeUpdate();
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public List<ScoreModel> select(ScoreModel model){
		StringBuilder sql = new StringBuilder("select score,name from achievements where 1=1 ");
		List<Object> list = appendWhere(sql,model);
		ResultSet rs = null;
		List<ScoreModel> result = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			for(int i=0;i<list.size();i++) {
				ps.setObject(i+1,list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				String score = rs.getString("score");
				String name = rs.getString("name");
				result.add(new ScoreModel(score,name));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	private List<Object> appendWhere(StringBuilder sql, ScoreModel model) {
		List<Object> list = new ArrayList<>(); 
		String score = model.getScore();
		if(score!=null && !score.trim().isEmpty()) {
			sql.append(" and score like ? ");
			list.add(score);
		}
		String name = model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		return list;
	}
}
